/*********************************/
* Table 2 results
* number of procedures and per-capita utilization rates (per 100,000 per year)
* inputs:
*	indata = the cohort tables saved in the output folder
*	population_nsw = the population table (abs)
*	population_on = ontario population table (for standardization);
/**********************************/

/* convert populations */
proc import out = codes.population_nsw datafile = 'C:\Users\kcha0642\Documents\NSW-ON-NY\on-ny-nsw-kcha0642\on-ny-nsw\population_table' 
	dbms = xlsx replace; 
	getnames = yes; 
run; 

proc import out = codes.population_ontario datafile = 'C:\Users\kcha0642\Documents\NSW-ON-NY\on-ny-nsw-kcha0642\on-ny-nsw\population_ontario' 
	dbms = xlsx replace; 
	getnames = yes; 
run; 

* nsw population in age and sex group;
* age groups ; 
proc format; 
	value age_fmt
	18 - 49 = '18-49'
	50 - 59 = '50-59'
	60 - 69 = '60-69'
	70 - 79 = '70-79'
	80 - 120 = '80+'; 
run;  

* sex formats ; 
OPTIONS FMTSEARCH=( Library.Formats );
data codes.population_nsw; 
	set codes.population_nsw; 
	format sex $HIRDSEX_1044_33.; 
run; 

*age group formats ; 
data codes.population_nsw; 
	set codes.population_nsw; 
	format age $7.; 
run; 

data codes.population_ontario; 
	set codes.population_ontario; 
	format agegp $7.; 
run; 
data codes.population_ontario; 
	set codes.population_ontario; 
	sex_c = put(sex,$1.); 
run; 
data codes.population_ontario; 
	set codes.population_ontario; 
	format sex_c $HIRDSEX_1044_33.; 
run; 

%macro table_two (indata, population_nsw, population_on, label);
**********************************;
/* number of procedures */ 

proc freq data = &indata; 
	table sex; 
run; 

**********************************;
/* adjusted utilisation rate */

data &indata; 
	set &indata; 
	age_group = put(age, age_fmt.); 
run; 

proc freq data = &indata ; 
	table age_group ; 
run; 

proc freq data = &indata ; 
	table age_group*sex / out = procedure_counts; 
run; 

* procedures per year ; 

** 5 years 3 months = 63 months; 
data procedure_counts ;
	set procedure_counts;
	rate_year = count / (63/12); 
run; 

* age - sex counts; 
proc sql; 
	create table pop_nsw_agesex as
	select age, sex, sum(count) as population
	from &population_nsw
	group by age,sex; 

proc sql; 
	create table temp as
	select a.age_group, a.sex, a.rate_year, b.population as nsw_population
	from procedure_counts as a
	left join pop_nsw_agesex as b
	on a.sex = b.sex and a.age_group = b.age; 

* population specific rate; 
data temp; 
	set temp; 
	age_sex_specific_rate = rate_year/nsw_population; 
run; 

* count in standard (ontario) population;
proc sql; 
	create table temp2 as
	select a.*, b.sum_pop as ontario_population
	from temp as a
	left join &population_on as b
	on a.sex = b.sex_c and a.age_group = b.agegp; 

* age and sex population rates; 
data temp2; 
	set temp2; 
	standard_pop_rate = age_sex_specific_rate*ontario_population; 
run; 

* export these tables for country comparison; 
proc export 
  data = temp2 
  dbms=xlsx 
  outfile="Z:\LowValueCare\APDC\on-ny-nsw\&label. adjusted counts" 
  replace;
run;

* total utilisation rates; 
proc tabulate data = temp2 out = total_counts; 
	class sex; 
	var standard_pop_rate ontario_population; 
	table sex ALL, standard_pop_rate ontario_population; 
run; 

data total_counts; 
	set total_counts; 
	asr = (standard_pop_rate_Sum / ontario_population_Sum ) *100000;
run; 

title "Total age-sex adjusted rates by sex"; 
proc print data = total_counts(keep=sex asr); 
run; 

%mend table_two; 
